package com.moss.street.market;
/*
 * Created on Nov 24, 2011.
 *   
 * Modeled after code in the tutorial "Build Web applications with Eclipse,
 * WTP, and Derby" by Susan L. Cline at
 * http://www.ibm.com/developerworks/data/library/techarticle/dm-0509cline/
 */


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.Collections;
import java.util.List;
import java.util.Properties;
import java.util.ArrayList;


/**
 * This class manages all communication between the database and controller/view levels.
 * 
 * @author drusk
 */
public class DerbyDatabase {
	private static String dbLocation = "MossStreetDB";
	private static String dbURL = "jdbc:derby://localhost:1527/";
	private static Connection conn = null;
	private static Statement stmt = null;
	private static String driverName = "org.apache.derby.jdbc.ClientDriver";

	/**
	 * Establishes connection to database.
	 * @return Connecton
	 */
	public static Connection getConnInstance() {
		if (conn == null) {
			conn = createConnection("MSS", "MSS");
		}
		return conn;
	}

	/**
	 * 
	 * @param userName
	 * @param password
	 * @return Connection
	 */
	private static Connection createConnection(String userName, String password) {
		try {
			Class.forName(driverName);
			Properties dbProps = new Properties();
			dbProps.put("user", userName);
			dbProps.put("password", password);
			conn = DriverManager.getConnection(dbURL + dbLocation, dbProps);
			System.err.println("Connection successful!");
		} catch (Exception except) {
			System.err.print("Could not connect to the database with username: "
					+ userName);
			System.err.println(" password " + password);
			System.err.println("Check that the Derby Network Server is running on localhost.");
			except.printStackTrace();
		}
		return conn;
	}

	/**
	 * Determines whether username and password are linked to a staff account.  If so then
	 * admin screens will be available.  Used by AdminServlet and LoginServlet.
	 * @param conn
	 * @param username
	 * @param password
	 * @return boolean true if a staff member, otherwise false
	 */
	public static boolean isStaffMember(Connection conn, String username, String password) {
		boolean result = false;
		
		UsersBean[] users = accountQuery(conn, username, password);
		if (users != null && users.length == 1) {
			result = users[0].getIsStaffMemberYN().equals("Y") ? true : false ;
		}
		return result;
	}
	
	/**
	 * Determine whether a user has entered a username and password that match
	 * an existing database account record.  Used by LoginServlet.
	 * @param conn
	 * @param username
	 * @param password
	 * @return Array with results of looking up account by username and password.
	 * 	Array should contain one result if account exists and password is correct.
	 */
	public static UsersBean[] accountQuery(Connection conn, String username,
			String password) {
		String query = "select * from MSS.Users where username = ? and password = ?";

		List list = Collections.synchronizedList(new ArrayList(1));

		try {
			PreparedStatement preparedStatement = conn.prepareStatement(query);
			preparedStatement.setString(1, username);
			preparedStatement.setString(2, password);

			ResultSet results = preparedStatement.executeQuery();

			while (results.next()) {
				String userIdFromDB = String.valueOf(results.getInt(1));
				String usernameFromDB = results.getString(2);
				String passwordFromDB = results.getString(3);
				String lastNameFromDB = results.getString(4);
				String firstNameFromDB = results.getString(5);
				String isEmployeeYN = results.getString(6);
				String emailAddress = results.getString(7);
				String contactInfo = results.getString(8);
				list.add(new UsersBean(userIdFromDB, usernameFromDB, passwordFromDB,
						lastNameFromDB, firstNameFromDB, isEmployeeYN, emailAddress,
						contactInfo));
			}
			results.close();
			preparedStatement.close();

		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}

		return (UsersBean[]) list.toArray(new UsersBean[list.size()]);
	}
	
	/**
	 * Get account information for a specific username.  Used by AdminServlet
	 * and LoginServlet.
	 * @param conn
	 * @param username
	 * @return Array with results of looking up account by username.
	 * 	Array should contain one result if account exists.
	 */
	public static UsersBean[] accountQuery(Connection conn, String username) {
		String query = "select * from MSS.Users where username = ? ORDER BY USER_ID";

		List list = Collections.synchronizedList(new ArrayList(1));

		try {
			PreparedStatement preparedStatement = conn.prepareStatement(query);
			preparedStatement.setString(1, username);

			ResultSet results = preparedStatement.executeQuery();

			while (results.next()) {
				String userIdFromDB = String.valueOf(results.getInt(1));
				String usernameFromDB = results.getString(2);
				String passwordFromDB = results.getString(3);
				String lastNameFromDB = results.getString(4);
				String firstNameFromDB = results.getString(5);
				String isEmployeeYN = results.getString(6);
				String emailAddress = results.getString(7);
				String contactInfo = results.getString(8);
				list.add(new UsersBean(userIdFromDB, usernameFromDB, passwordFromDB,
						lastNameFromDB, firstNameFromDB, isEmployeeYN, emailAddress,
						contactInfo));
			}
			results.close();
			preparedStatement.close();

		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}

		return (UsersBean[]) list.toArray(new UsersBean[list.size()]);
	}

	/**
	 * 	Adds new user account.  All fields must be specified.  Used by
	 *  AdminServlet and LoginServlet.
	 * @param conn
	 * @param username
	 * @param password
	 * @param lastName
	 * @param firstName
	 * @param isEmployeeYN
	 * @param emailAddress
	 * @param contactInfo
	 * @return 1 if database insert successful, -1 otherwise.
	 */
	public static int accountUpdate(Connection conn, String username,
			String password, String lastName, String firstName, String isEmployeeYN,
			String emailAddress, String contactInfo) {
		
		int updates = -1;
		// don't allow empty strings to be inserted
		if (username.isEmpty() || password.isEmpty() || lastName.isEmpty() ||
				firstName.isEmpty() || isEmployeeYN.isEmpty() ||
				emailAddress.isEmpty() || contactInfo.isEmpty() ) {
			return updates;
		}
		String query = "insert into MSS.USERS (username, password, last_name, first_name, is_staffmember_yn, email_address, contact_info) values (?, ?, ?, ?, ?, ?, ?)";
		try {
			PreparedStatement preparedStatement = conn.prepareStatement(query);
			preparedStatement.setString(1, username);
			preparedStatement.setString(2, password);
			preparedStatement.setString(3, lastName);
			preparedStatement.setString(4, firstName);
			preparedStatement.setString(5, isEmployeeYN);
			preparedStatement.setString(6, emailAddress);
			preparedStatement.setString(7, contactInfo);

			updates = preparedStatement.executeUpdate();
			
		} catch (SQLIntegrityConstraintViolationException duplicateExcept) {
			// username already exists, do nothing
		} catch (SQLException sqlExcept) {
			// log other types of exceptions
			System.err.println("SQLException: " + sqlExcept.getMessage());
		}

		return updates;
	}
	
	/**
	 * Used to update fields for a fixed userId, username record.  Not all fields
	 * need be specified.  Used by AdminServlet.
	 * 
	 * @param conn
	 * @param userId	must be specified
	 * @param username	must be specified
	 * @param password	only one of the remaining parameters needs to be specified
	 * @param lastName
	 * @param firstName
	 * @param isEmployeeYN
	 * @param emailAddress
	 * @param contactInfo
	 * @return 1 if database insert successful, -1 otherwise
	 */
	public static int accountUpdate(Connection conn, String userId, String username,
			String password, String lastName, String firstName, String isEmployeeYN,
			String emailAddress, String contactInfo) {
		
		int numRows = -1;
		// just return if there is nothing to do and
		// don't let the primary admin account disable itself!
		if (userId .isEmpty() || username.isEmpty() ||
				(password.isEmpty() && lastName.isEmpty() &&
				firstName.isEmpty() && isEmployeeYN.isEmpty() &&
				emailAddress.isEmpty() && contactInfo.isEmpty()) ||
				(Integer.valueOf(userId)==1 && !isEmployeeYN.isEmpty())) {
			return numRows;
		}
		
		boolean prior = false;
		//insert into MSS.USERS (username, password, last_name, first_name, is_staffmember_yn, email_address, contact_info
		StringBuilder sql = new StringBuilder();
		sql.append("update MSS.USERS set ");
		if (!password.isEmpty()) {
			sql.append("password ='"+password+"'");
			prior = true;
		}
		if (!lastName.isEmpty()) {
			if (prior) sql.append(", ");
			sql.append("last_name ='"+lastName+"'");
			prior = true;
		}
		if (!firstName.isEmpty()) {
			if (prior) sql.append(", ");
			sql.append("first_name ='"+firstName+"'");
			prior = true;
		}
		if (!isEmployeeYN.isEmpty()) {
			if (prior) sql.append(", ");
			sql.append("is_staffmember_yn ='"+isEmployeeYN+"'");
			prior = true;
		}
		if (!emailAddress.isEmpty()) {
			if (prior) sql.append(", ");
			sql.append("email_address ='"+emailAddress+"'");
			prior = true;
		}
		if (!contactInfo.isEmpty()) {
			if (prior) sql.append(", ");
			sql.append("contact_info ='"+contactInfo+"'");
			prior = true;
		}
		sql.append(" where user_id="+userId+" and username='"+username+"'");


		try {
			stmt = conn.createStatement();
			numRows = stmt.executeUpdate(sql.toString());
			stmt.close();
		} catch (SQLException sqlExcept) {
			System.err.println(sql.toString());
			System.err.println("SQLException: " + sqlExcept.getMessage());
		}
		
		return numRows;
	}
	

	/**
	 * List containing all stalls and their attributes.  Used by CheckBookingsServlet,
	 * 	AddStall.jsp and Welcome.jsp
	 * @param conn
	 * @return array of stallsbean
	 */
	public static StallsBean[] stallsList(Connection conn) {
		String query = "SELECT STALL_NUMBER, STALL_TYPE, FEE, LENGTH, WIDTH FROM MSS.STALLS ORDER BY STALL_NUMBER";
		List list = Collections.synchronizedList(new ArrayList(200));

		try {
			stmt = conn.createStatement();
			ResultSet results = stmt.executeQuery(query);

			while (results.next()) {
				String stallNumber = String.valueOf(results.getInt(1));
				String stallType = results.getString(2);
				String fee = String.valueOf(results.getInt(3));
				String length = String.valueOf(results.getInt(4));
				String width = String.valueOf(results.getInt(5));

				list.add(new StallsBean(stallNumber, stallType, fee, length,
						width));
			}
			results.close();
			stmt.close();
		} catch (SQLException sqlExcept) {
			System.err.println("SQLException: " + sqlExcept.getMessage());
		}

		return (StallsBean[]) list.toArray(new StallsBean[list.size()]);
	}

	/**
	 * Get details of a specific stall.  Used by AdminServlet and CheckBookingsServlet.
	 * 
	 * @param conn
	 * @param stallNumber
	 * @return a stallsbean
	 */
	public static StallsBean getStallDetails(Connection conn, String stallNumber) {

		String query = 
				"SELECT STALL_NUMBER, STALL_TYPE, FEE, LENGTH, WIDTH FROM MSS.STALLS WHERE STALL_NUMBER = ?";
		List list = Collections.synchronizedList(new ArrayList(200));

		try {
			PreparedStatement preparedStatement = conn.prepareStatement(query);
			preparedStatement.setString(1, stallNumber);
			
			ResultSet results = preparedStatement.executeQuery();
			
			while (results.next()) {
				String stallNumberStr = String.valueOf(results.getInt(1));
				String stallType = results.getString(2);
				String fee = String.valueOf(results.getInt(3));
				String length = String.valueOf(results.getInt(4));
				String width = String.valueOf(results.getInt(5));

				list.add(new StallsBean(stallNumber, stallType, fee, length,
						width));
			}
			results.close();
			preparedStatement.close();
		} catch (SQLException sqlExcept) {
			System.err.println("SQLException: " + sqlExcept.getMessage());
		}
		if (list.size() == 1) {
			return (StallsBean)list.get(0);
		} else {
			return null;
		}
	}

	/**
	 * Adds stalls.  Used by AdminServlet.
	 * @param conn
	 * @param stallNumber
	 * @param stallType
	 * @param stallLength
	 * @param stallWidth
	 * @param stallFee
	 * @return 1 if successful, -1 otherwise
	 */
	public static int addStall(Connection conn, String stallNumber, String stallType,
			String stallLength, String stallWidth, String stallFee) {
		int updates = -1;
		String query = "insert into MSS.STALLS (STALL_NUMBER, STALL_TYPE, LENGTH, WIDTH, FEE) values (?, ?, ?, ?, ?)";
		try {
			PreparedStatement preparedStatement = conn.prepareStatement(query);
			preparedStatement.setString(1, stallNumber);
			preparedStatement.setString(2, stallType);
			preparedStatement.setString(3, stallLength);
			preparedStatement.setString(4, stallWidth);
			preparedStatement.setString(5, stallFee);
			
			updates = preparedStatement.executeUpdate();
			
		} catch (SQLIntegrityConstraintViolationException duplicateExcept) {
			// stall number already exists, do nothing
		} catch (SQLException sqlExcept) {
			// log other types of exceptions
			System.err.println("SQLException: " + sqlExcept.getMessage());
		}

		return updates;
	}
	
	/**
	 * Allows one or more fields of a stall record to be updated.  The stall
	 * number can't be changed.  Not all field need be populated.  Used by AdminServlet.
	 * @param conn
	 * @param stallNumber
	 * @param stallType
	 * @param stallLength
	 * @param stallWidth
	 * @param stallFee
	 * @return 1 if successful, -1 otherwise
	 */
	public static int modifyStall(Connection conn, String stallNumber, String stallType,
			String stallLength, String stallWidth, String stallFee) {
		int numRows = -1;
		//insert into MSS.STALLS (STALL_NUMBER, STALL_TYPE, LENGTH, WIDTH, FEE) values (?, ?, ?, ?, ?)

		// just return if there is nothing to do
		if (stallNumber .isEmpty() || 
				(stallType.isEmpty() && stallLength.isEmpty() &&
						stallWidth.isEmpty() && stallFee.isEmpty())) {
			return numRows;
		}
		
		boolean prior = false;

		StringBuilder sql = new StringBuilder();
		sql.append("update MSS.STALLS set ");
		if (!stallType.isEmpty()) {
			sql.append("stall_type ='"+stallType+"'");
			prior = true;
		}
		if (!stallLength.isEmpty()) {
			if (prior) sql.append(", ");
			sql.append("length ="+stallLength);
			prior = true;
		}
		if (!stallWidth.isEmpty()) {
			if (prior) sql.append(", ");
			sql.append("width ="+stallWidth);
			prior = true;
		}
		if (!stallFee.isEmpty()) {
			if (prior) sql.append(", ");
			sql.append("fee ="+stallFee);
			prior = true;
		}
		sql.append(" where stall_number="+stallNumber);

		try {
			stmt = conn.createStatement();
			numRows = stmt.executeUpdate(sql.toString());
			stmt.close();
		} catch (SQLException sqlExcept) {
			System.err.println("SQL="+sql.toString());
			System.err.println("SQLException: " + sqlExcept.getMessage());
		}
		
		return numRows;
	}
	
	/**
	 * List of all users.  Used by AdminAddUser.jsp
	 * @param conn
	 * @return array of usersbean
	 */
	public static UsersBean[] usersList(Connection conn) {
		String query = "select USER_ID, USERNAME, PASSWORD, LAST_NAME, FIRST_NAME, IS_STAFFMEMBER_YN, EMAIL_ADDRESS, CONTACT_INFO"
				+ " from MSS.USERS order by USER_ID";
		List list = Collections.synchronizedList(new ArrayList(10));

		try {
			stmt = conn.createStatement();
			ResultSet results = stmt.executeQuery(query);

			while (results.next()) {
				String userId = String.valueOf(results.getInt(1));
				String username = results.getString(2);
				String password = results.getString(3);
				String lastName = results.getString(4);
				String firstName = results.getString(5);
				String isStaffMemberYN = results.getString(6);
				String emailAddress = results.getString(7);
				String contactInfo = results.getString(8);


				list.add(new UsersBean(userId, username, password, lastName, firstName,
						isStaffMemberYN, emailAddress, contactInfo));
			}
			results.close();
			stmt.close();
		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}

		return (UsersBean[]) list.toArray(new UsersBean[list.size()]);
	}

	/**
	 * Return an array of all bookings.  Used by AdminAddBooking.jsp.
	 * @param conn
	 * @return array of bookingsbean
	 */
	public static BookingsBean[] fetchAllBookings(Connection conn) {
		String query = "select USERNAME, STALL_NUMBER, DATE_RESERVED, IS_MAINTENANCE_YN, DATE_BOOKING_MADE"
				+ " from MSS.BOOKINGS ORDER BY DATE_RESERVED, STALL_NUMBER";
		List list = Collections.synchronizedList(new ArrayList(10));

		try {
			stmt = conn.createStatement();
			ResultSet results = stmt.executeQuery(query);

			while (results.next()) {
				String userNameStr = results.getString(1);
				String stallNumber = String.valueOf(results.getInt(2));
				String dateReserved = String.valueOf(results.getDate(3));
				String isMaintenanceYN = results.getString(4);
				String dateBookingMade = String.valueOf(results.getDate(5));

				list.add(new BookingsBean(userNameStr, stallNumber, dateReserved,
						isMaintenanceYN, dateBookingMade));
			}
			stmt.close();
			results.close();
		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}

		return (BookingsBean[]) list.toArray(new BookingsBean[list.size()]);
	}
	
	/**
	 * All bookings held by a specific user.  Used by UpdateBookingServlet.
	 * @param conn
	 * @param username
	 * @return array of bookingsbean
	 */
	public static BookingsBean[] fetchBookings(Connection conn, String username) {
		String query = "select USERNAME, STALL_NUMBER, DATE_RESERVED, IS_MAINTENANCE_YN, DATE_BOOKING_MADE"
				+ " from MSS.BOOKINGS where USERNAME = ? ORDER BY DATE_RESERVED";
		List list = Collections.synchronizedList(new ArrayList(10));

		try {
			PreparedStatement prepStmt = conn.prepareStatement(query);
			prepStmt.setString(1, username);
			ResultSet results = prepStmt.executeQuery();

			while (results.next()) {
				String userNameStr = results.getString(1);
				String stallNumber = String.valueOf(results.getInt(2));
				String dateReserved = String.valueOf(results.getDate(3));
				String isMaintenanceYN = results.getString(4);
				String dateBookingMade = String.valueOf(results.getDate(5));

				list.add(new BookingsBean(userNameStr, stallNumber, dateReserved,
						isMaintenanceYN, dateBookingMade));
			}
			results.close();
			prepStmt.close();
		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}

		return (BookingsBean[]) list.toArray(new BookingsBean[list.size()]);
	}
	
	/**
	 * Stall booking for a specific date.  Used by AdminServlet. 
	 * @param conn
	 * @param stallNumber
	 * @param dateReserved
	 * @return an array which should contain one booking if the stall has been reserved on that date
	 */
	public static BookingsBean[] fetchBookings(
			Connection conn, String stallNumber, String dateReserved) {
		String query = "select USERNAME, STALL_NUMBER, DATE_RESERVED, IS_MAINTENANCE_YN, DATE_BOOKING_MADE"
				+ " from MSS.BOOKINGS where STALL_NUMBER = ? AND DATE_RESERVED = ? ORDER BY DATE_RESERVED";
		List list = Collections.synchronizedList(new ArrayList(10));

		try {
			PreparedStatement prepStmt = conn.prepareStatement(query);
			prepStmt.setString(1, stallNumber);
			prepStmt.setString(2, dateReserved);
			ResultSet results = prepStmt.executeQuery();

			while (results.next()) {
				String userNameStr = results.getString(1);
				String stallNumberStr = String.valueOf(results.getInt(2));
				String dateReservedStr = String.valueOf(results.getDate(3));
				String isMaintenanceYN = results.getString(4);
				String dateBookingMade = String.valueOf(results.getDate(5));

				list.add(new BookingsBean(userNameStr, stallNumberStr, dateReservedStr,
						isMaintenanceYN, dateBookingMade));
			}
			results.close();
			prepStmt.close();
		} catch (SQLException sqlExcept) {
			System.err.println("Bad query with STALL_NUMBER="+stallNumber+
					" and DATE_RESERVED="+dateReserved);
			System.err.println("Query was query="+query);
			System.err.println("SQLException: " + sqlExcept.getMessage());
			//sqlExcept.printStackTrace();
		}

		return (BookingsBean[]) list.toArray(new BookingsBean[list.size()]);
	}
	
	/**
	 * Get all booking dates for a specific stall.  Used by CheckBookingsServlet.
	 * @param conn
	 * @param stallNumber
	 * @return array of bookings for a stall
	 */
	public static BookingsBean[] fetchBookingsByStall(Connection conn, String stallNumber) {
		String query = "select USERNAME, STALL_NUMBER, DATE_RESERVED, IS_MAINTENANCE_YN, DATE_BOOKING_MADE"
				+ " from MSS.BOOKINGS where STALL_NUMBER = ? ORDER BY DATE_RESERVED, STALL_NUMBER";
		List list = Collections.synchronizedList(new ArrayList(10));

		try {
			PreparedStatement prepStmt = conn.prepareStatement(query);
			prepStmt.setString(1, stallNumber);
			ResultSet results = prepStmt.executeQuery();

			while (results.next()) {
				String userNameStr = results.getString(1);
				String stallNumberStr = String.valueOf(results.getInt(2));
				String dateReserved = String.valueOf(results.getDate(3));
				String isMaintenanceYN = results.getString(4);
				String dateBookingMade = String.valueOf(results.getDate(5));

				list.add(new BookingsBean(userNameStr, stallNumberStr, dateReserved,
						isMaintenanceYN, dateBookingMade));
			}
			results.close();
			prepStmt.close();
		} catch (SQLException sqlExcept) {
			System.err.println("Bad query with STALL_NUMBER="+stallNumber);
			System.err.println("Query was query="+query);
			System.err.println("SQLException: " + sqlExcept.getMessage());
			//sqlExcept.printStackTrace();
		}

		return (BookingsBean[]) list.toArray(new BookingsBean[list.size()]);
	}
		
	/**
	 * All stall bookings for specific market date.  Used by CheckBookingsServlet
	 * @param conn
	 * @param marketday
	 * @return array of all bookings for a specific date
	 */
	public static BookingsBean[] fetchBookingsByDate(Connection conn, String marketday) {
		String query = "select USERNAME, STALL_NUMBER, DATE_RESERVED, IS_MAINTENANCE_YN, DATE_BOOKING_MADE"
					+ " from MSS.BOOKINGS where DATE_RESERVED = ? ORDER BY STALL_NUMBER";
		List list = Collections.synchronizedList(new ArrayList(10));

		try {
			PreparedStatement prepStmt = conn.prepareStatement(query);
			prepStmt.setString(1, marketday);
			ResultSet results = prepStmt.executeQuery();

			while (results.next()) {
				String userNameStr = results.getString(1);
				String stallNumberStr = String.valueOf(results.getInt(2));
				String dateReserved = String.valueOf(results.getDate(3));
				String isMaintenanceYN = results.getString(4);
				String dateBookingMade = String.valueOf(results.getDate(5));

				list.add(new BookingsBean(userNameStr, stallNumberStr, dateReserved,
						isMaintenanceYN, dateBookingMade));
			}
			results.close();
			prepStmt.close();
		} catch (SQLException sqlExcept) {
			System.err.println("Bad query with DATE_RESERVED="+marketday);
			System.err.println("Query was query="+query);
			System.err.println("SQLException: " + sqlExcept.getMessage());
			//sqlExcept.printStackTrace();
		}

		return (BookingsBean[]) list.toArray(new BookingsBean[list.size()]);
	}
	
	/**
	 * Adds a new booking if all information is provided.
	 * Used by UpdateBookingServlet and AdminServlet.
	 * @param bookingForUserName
	 * @param bookingStallNumber
	 * @param dateReserved
	 * @param maintenanceYN
	 * @param dateBookingMade
	 * @return 1 if successful, -1 otherwise
	 */
	public static int bookingsUpdate(Connection conn, String bookingForUserName,
			String bookingStallNumber, String dateReserved, String maintenanceYN,
			String dateBookingMade) {
		int updates = -1;
		
		if (bookingForUserName.isEmpty() || bookingStallNumber.isEmpty() ||
				dateReserved.isEmpty() || maintenanceYN.isEmpty() || dateBookingMade.isEmpty()) {
			return updates;
		}
        String sql = "insert into MSS.BOOKINGS (username, stall_number, date_reserved, is_maintenance_yn, date_booking_made)" +
                " values (?, ?, ?, ?, ?)";

		try {
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, bookingForUserName);
			preparedStatement.setString(2, bookingStallNumber);
			preparedStatement.setString(3, dateReserved);
			preparedStatement.setString(4, maintenanceYN);
			preparedStatement.setString(5, dateBookingMade);

			updates = preparedStatement.executeUpdate();
			
		} catch (SQLIntegrityConstraintViolationException duplicateExcept) {
			// prior booking of stall on reservation date already exists, do nothing
		} catch (SQLException sqlExcept) {
			// log other types of exceptions
			System.err.println("SQLException: " + sqlExcept.getMessage());
		}

		return updates;
	}
	
	/**
	 * Delete a booking if all parameters match.  Used by AdminServlet.java.
	 * @param bookingForUserName
	 * @param bookingStallNumber
	 * @param dateReserved
	 * @param maintenanceYN
	 * @param dateBookingMade
	 * @return 1 if successful, -1 otherwise
	 */
	public static int bookingsDelete(Connection conn, String bookingForUserName,
			String bookingStallNumber, String dateReserved, String maintenanceYN,
			String dateBookingMade) {
		int updates = -1;
		
		if (bookingForUserName.isEmpty() || bookingStallNumber.isEmpty() ||
				dateReserved.isEmpty() || maintenanceYN.isEmpty() || dateBookingMade.isEmpty()) {
			return updates;
		}
        String sql = "delete from MSS.BOOKINGS where username = ? and stall_number = ? "+
        		" and date_reserved = ? and is_maintenance_yn = ? and date_booking_made = ?";

		try {
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, bookingForUserName);
			preparedStatement.setString(2, bookingStallNumber);
			preparedStatement.setString(3, dateReserved);
			preparedStatement.setString(4, maintenanceYN);
			preparedStatement.setString(5, dateBookingMade);

			updates = preparedStatement.executeUpdate();
			
		} catch (SQLIntegrityConstraintViolationException duplicateExcept) {
			// prior booking of stall on reservation date already exists, do nothing
		} catch (SQLException sqlExcept) {
			// log other types of exceptions
			System.err.println("SQLException: " + sqlExcept.getMessage());
		}

		return updates;
	}
	
	/**
	 * Determines whether specified stall is available on a specific date.  Used
	 * by CheckBookingsServlet.java.
	 * @param conn
	 * @param stallNumber
	 * @param dateStr
	 * @return true if available, false otherwise
	 */
	public static boolean isStallAvailable(Connection conn, String stallNumber, String dateStr) {
		if (stallNumber == null || stallNumber.isEmpty() || dateStr == null || dateStr.isEmpty()) {
			System.err.println("DerbyDatabase.isStallAvailable(conn, stallNumber, dateStr))"+
					" called with bad parameters)");
			return false;
		}

		String query = "select USERNAME, STALL_NUMBER, DATE_RESERVED, IS_MAINTENANCE_YN, DATE_BOOKING_MADE"
				+ " from MSS.BOOKINGS where STALL_NUMBER = ? AND DATE_RESERVED = DATE('" + dateStr + "')";

		List list = Collections.synchronizedList(new ArrayList(10));
		try {
			PreparedStatement prepStmt = conn.prepareStatement(query);
			prepStmt.setString(1, stallNumber);
			ResultSet results = prepStmt.executeQuery();
			while (results.next()) {
				String userNameStr = results.getString(1);
				String stallNumberStr = String.valueOf(results.getInt(2));
				String dateReserved = String.valueOf(results.getDate(3));
				String isMaintenanceYN = results.getString(4);
				String dateBookingMade = String.valueOf(results.getDate(5));

				list.add(new BookingsBean(userNameStr, stallNumberStr, dateReserved,
						isMaintenanceYN, dateBookingMade));
			}
			results.close();
			prepStmt.close();
		} catch (SQLException sqlExcept) {
			System.err.println("SQLException: " + sqlExcept.getMessage());
			return false;
		}
		if (list.size() == 0) {
			return true;
		} else {
			return false;
		}
	}
	
	/**
	 * Get userId by username
	 * @param conn
	 * @param username
	 * @return string containing userId
	 */
	public static String getUserIdByUsername(Connection conn, String username) {
		String query = "SELECT USER_ID FROM MSS.USERS WHERE USERNAME=?";
		String userId = "";
		try {
			PreparedStatement prepStmt = conn.prepareStatement(query);
			prepStmt.setString(1, username);
			ResultSet results = prepStmt.executeQuery();

			while (results.next()) {
				userId = String.valueOf(results.getInt(1));
			}
			results.close();
			prepStmt.close();
		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}
		return userId;
	}

}
